import os
from urllib.parse import urlparse
import pymysql
from intelli_port.data_layer.clients import mysql_execute_write, mysql_execute_read, mysql_transaction, redis_client, milvus_client, neo4j_exec
from intelli_port.rag.embeddings import embed_text
from intelli_port.rag.vector_rag import sync_kb_to_milvus

def _parse_mysql_uri(uri: str):
    u = urlparse(uri or "")
    return {
        "host": u.hostname or "",
        "port": u.port or 3306,
        "user": u.username or "",
        "password": u.password or "",
        "db": (u.path or "/").lstrip("/") or None,
    }

def ensure_database():
    uri = os.getenv("MYSQL_URI", "")
    if not uri:
        return False
    p = _parse_mysql_uri(uri)
    if not p["db"]:
        return False
    conn = None
    try:
        conn = pymysql.connect(host=p["host"], port=p["port"], user=p["user"], password=p["password"])
        cur = conn.cursor()
        cur.execute(f"CREATE DATABASE IF NOT EXISTS `{p['db']}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
        conn.commit()
        cur.close()
        return True
    except Exception:
        try:
            if conn:
                conn.close()
        except Exception:
            pass
        return False

def create_tables():
    stmts = [
        """
        CREATE TABLE IF NOT EXISTS terminals (
            id BIGINT PRIMARY KEY AUTO_INCREMENT,
            name VARCHAR(64) NOT NULL
        )
        """,
        """
        CREATE TABLE IF NOT EXISTS gates (
            id BIGINT PRIMARY KEY AUTO_INCREMENT,
            code VARCHAR(16) NOT NULL,
            terminal_id BIGINT NOT NULL,
            INDEX idx_gate_code(code)
        )
        """,
        """
        CREATE TABLE IF NOT EXISTS flights (
            id BIGINT PRIMARY KEY AUTO_INCREMENT,
            flight_no VARCHAR(32) NOT NULL,
            airline VARCHAR(64) NOT NULL,
            gate_id BIGINT,
            origin VARCHAR(64),
            destination VARCHAR(64),
            departure_time DATETIME,
            arrival_time DATETIME,
            status ENUM('scheduled','boarding','delayed','canceled','landed')
        )
        """,
        """
        CREATE TABLE IF NOT EXISTS passengers (
            id BIGINT PRIMARY KEY AUTO_INCREMENT,
            name VARCHAR(64) NOT NULL,
            phone VARCHAR(32),
            ticket_no VARCHAR(64),
            flight_id BIGINT,
            language VARCHAR(16)
        )
        """,
        """
        CREATE TABLE IF NOT EXISTS robots (
            id BIGINT PRIMARY KEY AUTO_INCREMENT,
            serial VARCHAR(64) NOT NULL,
            model VARCHAR(64),
            status VARCHAR(32),
            location VARCHAR(64),
            battery INT,
            last_heartbeat DATETIME
        )
        """,
        """
        CREATE TABLE IF NOT EXISTS service_requests (
            id BIGINT PRIMARY KEY AUTO_INCREMENT,
            passenger_id BIGINT,
            service_type VARCHAR(64) NOT NULL,
            priority INT DEFAULT 0,
            status ENUM('pending','assigned','completed','canceled') NOT NULL,
            robot_id BIGINT,
            location VARCHAR(64),
            created_at DATETIME,
            assigned_at DATETIME,
            completed_at DATETIME,
            notes TEXT
        )
        """,
        """
        CREATE TABLE IF NOT EXISTS robot_tasks (
            id BIGINT PRIMARY KEY AUTO_INCREMENT,
            robot_id BIGINT NOT NULL,
            type VARCHAR(64) NOT NULL,
            payload TEXT,
            priority INT DEFAULT 0,
            status ENUM('scheduled','running','finished','canceled') NOT NULL,
            scheduled_at DATETIME,
            started_at DATETIME,
            finished_at DATETIME
        )
        """,
        """
        CREATE TABLE IF NOT EXISTS ops_logs (
            id BIGINT PRIMARY KEY AUTO_INCREMENT,
            level VARCHAR(16) NOT NULL,
            message TEXT,
            context JSON,
            created_at DATETIME
        )
        """,
        """
        CREATE TABLE IF NOT EXISTS kv_config (
            `key` VARCHAR(64) PRIMARY KEY,
            `value` TEXT,
            updated_at DATETIME
        )
        """,
        """
        CREATE TABLE IF NOT EXISTS knowledge_base (
            id BIGINT PRIMARY KEY AUTO_INCREMENT,
            question TEXT NOT NULL,
            answer TEXT NOT NULL,
            lang VARCHAR(8),
            tags VARCHAR(256),
            updated_at DATETIME
        )
        """,
        """
        CREATE TABLE IF NOT EXISTS flight_events (
            id BIGINT PRIMARY KEY AUTO_INCREMENT,
            flight_id BIGINT NOT NULL,
            event VARCHAR(32) NOT NULL,
            detail TEXT,
            created_at DATETIME
        )
        """,
        """
        CREATE TABLE IF NOT EXISTS robot_telemetry (
            id BIGINT PRIMARY KEY AUTO_INCREMENT,
            robot_id BIGINT NOT NULL,
            battery INT,
            location VARCHAR(64),
            temperature FLOAT,
            created_at DATETIME
        )
        """,
        """
        CREATE TABLE IF NOT EXISTS service_types (
            id BIGINT PRIMARY KEY AUTO_INCREMENT,
            code VARCHAR(64) UNIQUE,
            name VARCHAR(128)
        )
        """,
        """
        CREATE TABLE IF NOT EXISTS devices (
            id BIGINT PRIMARY KEY AUTO_INCREMENT,
            name VARCHAR(128),
            type VARCHAR(64),
            location VARCHAR(64),
            status VARCHAR(32),
            updated_at DATETIME
        )
        """,
        """
        CREATE TABLE IF NOT EXISTS alerts (
            id BIGINT PRIMARY KEY AUTO_INCREMENT,
            level VARCHAR(16),
            source VARCHAR(64),
            message TEXT,
            created_at DATETIME,
            acknowledged TINYINT DEFAULT 0
        )
        """,
    ]
    for s in stmts:
        mysql_execute_write(s)
    mysql_execute_write("CREATE INDEX IF NOT EXISTS idx_flights_gate_id ON flights(gate_id)")
    mysql_execute_write("CREATE INDEX IF NOT EXISTS idx_passengers_flight_id ON passengers(flight_id)")
    mysql_execute_write("CREATE INDEX IF NOT EXISTS idx_service_requests_status ON service_requests(status)")
    mysql_execute_write("CREATE INDEX IF NOT EXISTS idx_robot_tasks_status ON robot_tasks(status)")

def seed_sample_data():
    with mysql_transaction() as cur:
        if cur is None:
            return
        cur.execute("DELETE FROM flight_events")
        cur.execute("DELETE FROM robot_telemetry")
        cur.execute("DELETE FROM alerts")
        cur.execute("DELETE FROM devices")
        cur.execute("DELETE FROM service_requests")
        cur.execute("DELETE FROM robot_tasks")
        cur.execute("DELETE FROM passengers")
        cur.execute("DELETE FROM flights")
        cur.execute("DELETE FROM gates")
        cur.execute("DELETE FROM terminals")
        cur.execute("DELETE FROM knowledge_base")
        cur.execute("DELETE FROM kv_config")
    mysql_execute_write("INSERT INTO terminals(name) VALUES('T1'),('T2')")
    mysql_execute_write("INSERT INTO gates(code, terminal_id) VALUES('A12', 1),('B22', 2)")
    gate_a12 = mysql_execute_read("SELECT id FROM gates WHERE code=%s", ("A12",), fetch="one").get("rows", [])
    gate_b22 = mysql_execute_read("SELECT id FROM gates WHERE code=%s", ("B22",), fetch="one").get("rows", [])
    gate_a12_id = gate_a12[0][0] if gate_a12 else None
    gate_b22_id = gate_b22[0][0] if gate_b22 else None
    if gate_a12_id is None or gate_b22_id is None:
        return
    mysql_execute_write("INSERT INTO flights(flight_no, airline, gate_id, origin, destination, status, departure_time) VALUES(%s,%s,%s,%s,%s,%s,NOW()),(%s,%s,%s,%s,%s,%s,NOW())",
                        ("CA123","Air China",gate_a12_id,"SHA","PEK","boarding","MU456","China Eastern",gate_b22_id,"SHA","CTU","delayed"))
    flight_ca123 = mysql_execute_read("SELECT id FROM flights WHERE flight_no=%s", ("CA123",), fetch="one").get("rows", [])
    flight_mu456 = mysql_execute_read("SELECT id FROM flights WHERE flight_no=%s", ("MU456",), fetch="one").get("rows", [])
    flight_ca123_id = flight_ca123[0][0] if flight_ca123 else None
    flight_mu456_id = flight_mu456[0][0] if flight_mu456 else None
    mysql_execute_write("INSERT INTO passengers(name, phone, ticket_no, flight_id, language) VALUES(%s,%s,%s,%s,%s),(%s,%s,%s,%s,%s),(%s,%s,%s,%s,%s)",
                        ("张三","13800000001","CA123-001",flight_ca123_id,"zh","李四","13800000002","CA123-002",flight_ca123_id,"zh","Alice","13800000003","MU456-001",flight_mu456_id,"en"))
    mysql_execute_write("INSERT INTO robots(serial, model, status, location, battery) VALUES(%s,%s,%s,%s,%s),(%s,%s,%s,%s,%s),(%s,%s,%s,%s,%s)",
                        ("RB-001","GuideBot","idle","A12",80,"RB-002","CarryBot","charging","B22",45,"RB-003","PatrolBot","on_task","T1",60))
    robot_1 = mysql_execute_read("SELECT id FROM robots WHERE serial=%s", ("RB-001",), fetch="one").get("rows", [])
    robot_2 = mysql_execute_read("SELECT id FROM robots WHERE serial=%s", ("RB-002",), fetch="one").get("rows", [])
    robot_1_id = robot_1[0][0] if robot_1 else None
    robot_2_id = robot_2[0][0] if robot_2 else None
    mysql_execute_write("INSERT INTO service_types(code, name) VALUES('gate_guide','登机口引导'),('wheelchair','轮椅协助'),('luggage_guide','行李引导')")
    mysql_execute_write("INSERT INTO service_requests(passenger_id, service_type, priority, status, robot_id, location, created_at) VALUES(%s,%s,%s,%s,%s,%s,NOW()),(%s,%s,%s,%s,%s,%s,NOW()),(%s,%s,%s,%s,%s,%s,NOW())",
                        (1,'gate_guide',10,'pending',None,'A12',2,'wheelchair',5,'assigned',robot_1_id,'A12',3,'luggage_guide',5,'completed',robot_2_id,'B22'))
    mysql_execute_write("INSERT INTO robot_tasks(robot_id, type, payload, priority, status, scheduled_at) VALUES(%s,%s,%s,%s,%s,NOW()),(%s,%s,%s,%s,%s,NOW())",
                        (robot_1_id,'navigate','{"to": "A12"}',8,'scheduled',robot_2_id,'patrol','{"route": "T1"}',5,'scheduled'))
    mysql_execute_write("INSERT INTO flight_events(flight_id, event, detail, created_at) VALUES(1,'status','boarding',NOW()),(2,'status','delayed',NOW())")
    mysql_execute_write("INSERT INTO robot_telemetry(robot_id, battery, location, temperature, created_at) VALUES(1,80,'A12',36.5,NOW()),(2,45,'B22',37.1,NOW())")
    mysql_execute_write("INSERT INTO devices(name, type, location, status, updated_at) VALUES('FIDS-01','display','T1','ok',NOW()),('CAM-01','camera','T2','ok',NOW())")
    mysql_execute_write("INSERT INTO alerts(level, source, message, created_at, acknowledged) VALUES('warn','system','测试告警',NOW(),0)")
    mysql_execute_write("INSERT INTO knowledge_base(question, answer, lang, tags, updated_at) VALUES('CA123登机口在哪？','请前往 A12 登机口','zh','gate',NOW()),('Where is gate A12?','Go to gate A12','en','gate',NOW()),('航班延误怎么办？','请关注航显或咨询服务台','zh','delay',NOW()),('What if flight delayed?','Check FIDS or ask the desk','en','delay',NOW()),('如何申请轮椅？','请在服务台登记','zh','service',NOW())")
    mysql_execute_write("INSERT INTO kv_config(`key`, `value`, updated_at) VALUES('slo.response_p95_ms','300',NOW()),('feature.robot_autoschedule','true',NOW())")

def seed_redis_data():
    try:
        r = redis_client()
        if r:
            r.set("idempotency:test", "1")
            r.set("qwen_http_queue_len", "2")
    except Exception:
        pass

def seed_milvus_data():
    try:
        mc = milvus_client()
        if mc:
            try:
                mc.create_collection(collection_name="airport_vectors", dimension=4)
            except Exception:
                pass
            try:
                mc.insert(collection_name="airport_vectors", data=[[0.1,0.2,0.3,0.4],[0.2,0.1,0.5,0.3]])
            except Exception:
                pass
            try:
                mc.create_collection(collection_name="kb_vectors", dimension=4)
            except Exception:
                pass
            try:
                kb = [
                    "A12 登机口在哪",
                    "Where is gate A12",
                    "登机口 A12", 
                ]
                vecs = [embed_text(t) for t in kb]
                mc.insert(collection_name="kb_vectors", data=vecs)
            except Exception:
                pass
            try:
                sync_kb_to_milvus()
            except Exception:
                pass
    except Exception:
        pass

def seed_neo4j_data():
    try:
        neo4j_exec("MERGE (t:Terminal {name:$n})", {"n": "T1"})
        neo4j_exec("MERGE (g:Gate {code:$c})", {"c": "A12"})
        neo4j_exec("MATCH (t:Terminal {name:$n}),(g:Gate {code:$c}) MERGE (t)-[:HAS_GATE]->(g)", {"n": "T1", "c": "A12"})
        neo4j_exec("MERGE (p1:POI {name:$n})", {"n": "入口"})
        neo4j_exec("MERGE (p2:POI {name:$n})", {"n": "安检"})
        neo4j_exec("MERGE (p3:POI {name:$n})", {"n": "A12"})
        neo4j_exec("MERGE (p4:POI {name:$n})", {"n": "B22"})
        neo4j_exec("MATCH (a:POI {name:$a}),(b:POI {name:$b}) MERGE (a)-[e:PATH]->(b) SET e.distance_m=$d, e.time_s=$t, e.accessible=$acc, e.crowd_factor=$cf", {"a": "入口", "b": "安检", "d": 200.0, "t": 240.0, "acc": True, "cf": 1.2})
        neo4j_exec("MATCH (a:POI {name:$a}),(b:POI {name:$b}) MERGE (a)-[e:PATH]->(b) SET e.distance_m=$d, e.time_s=$t, e.accessible=$acc, e.crowd_factor=$cf", {"a": "安检", "b": "A12", "d": 500.0, "t": 600.0, "acc": True, "cf": 1.0})
        neo4j_exec("MATCH (a:POI {name:$a}),(b:POI {name:$b}) MERGE (a)-[e:PATH]->(b) SET e.distance_m=$d, e.time_s=$t, e.accessible=$acc, e.crowd_factor=$cf", {"a": "安检", "b": "B22", "d": 300.0, "t": 360.0, "acc": False, "cf": 1.1})
    except Exception:
        pass

def setup_all():
    ok = ensure_database()
    if not ok:
        return False
    create_tables()
    seed_sample_data()
    seed_redis_data()
    seed_milvus_data()
    seed_neo4j_data()
    return True